﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Passwords.Biz;
using System.Data.SqlServerCe;
using System.Data;
using System.Diagnostics;
using System.IO;

namespace Passwords.DAL
{
    public class DalContext : IDisposable, IDalContext
    {
        const string DataBaseName = "Passwords.sdf";
        private SqlCeConnection Connection;

        public static bool CheckPassword(string password)
        {
            try
            {
                DalContext context = new DalContext(password, true);
                context.Dispose();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public void Dispose()
        {
            if (Connection != null) Connection.Dispose();
        }

        private DalContext(string password, bool check)
            : base()
        {
            this.Connection = CreateConnection(password);
            Connection.Open();
        }

        public DalContext(string password)
            : this(password, false)
        {
        }

        public static bool ChangedPassword(string oldPassword, string newPassword)
        {
            try
            {
                string tempName = "Passwords.sdf.tmp";
                using (SqlCeEngine engine = new SqlCeEngine(CreateConnectionString(oldPassword, DataBaseName)))
                {
                    string newCon = CreateConnectionString(newPassword, tempName);
                    engine.Compact(newCon);
                }
                string src = GetConnectionPath(DataBaseName);
                string dst = GetConnectionPath(tempName);
                File.Delete(src);
                File.Move(dst, src);

                return true;
            }
            catch (Exception ex)
            {
                Trace.WriteLine(ex.Message);
                Trace.WriteLine(ex.StackTrace);
                return false;
            }
        }

        private static string GetConnectionPath(string databaseName)
        {
            return (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\" + databaseName);
        }

        private static string CreateConnectionString(string password, string databaseName)
        {
            string conStr = "Data Source =" + GetConnectionPath(databaseName) + "; Password=\"" + password + "\"";

            return conStr;
        }

        private static SqlCeConnection CreateConnection(string password)
        {
            return new SqlCeConnection(CreateConnectionString(password, DataBaseName));
        }

        #region IDalContext Members

        public IEnumerable<Category> GetCategories()
        {
            using (SqlCeCommand command = new SqlCeCommand("select id, name from Category order by name", Connection))
            {
                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return new Category(reader.GetInt32(0), reader.GetString(1));
                    }
                }
            }
        }

        private IEnumerable<Password> GetPasswordsCore()
        {
            using (SqlCeCommand command = new SqlCeCommand("select id, name, isFavorite from Password order by name", Connection))
            {
                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return new Password(reader.GetInt32(0), reader.GetString(1), reader.GetBoolean(2));
                    }
                }
            }
        }

        private IEnumerable<PasswordItem> GetPasswordItems(Password p)
        {
            using (SqlCeCommand command = new SqlCeCommand("select passwordId, value, labelId from PasswordItem where passwordid=@id ", Connection))
            {
                command.Parameters.AddWithValue("@id", p.Id);
                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string value = reader.GetString(1);
                        int labelId = reader.GetInt32(2);

                        yield return new PasswordItem(id, p, labelId, value);
                    }
                }
                yield return new CategoryPasswordItem(p);
            }
        }


        public IEnumerable<Password> GetPasswords()
        {
            var passwords = GetPasswordsCore().ToArray();
            foreach (var p in passwords)
            {
                p.Items = GetPasswordItems(p).ToArray();
            }
            return passwords;
        }


        public IEnumerable<CategoryLabel> GetCategoryLabels()
        {
            using (SqlCeCommand command = new SqlCeCommand("select CategoryId, LabelId from CategoryLabel", Connection))
            {
                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return new CategoryLabel(reader.GetInt32(0), reader.GetInt32(1));
                    }
                }
            }
        }

        public IEnumerable<PasswordCategory> GetPasswordCategories()
        {
            using (SqlCeCommand command = new SqlCeCommand("select CategoryId, PasswordId from PasswordCategory", Connection))
            {
                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return new PasswordCategory(reader.GetInt32(1), reader.GetInt32(0));
                    }
                }
            }
        }

        public IEnumerable<PasswordLabel> GetLabels()
        {
            using (SqlCeCommand command = new SqlCeCommand("select Id, [Name],Type,[Index] from Label", Connection))
            {
                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        int type = reader.GetInt16(2);
                        int index = reader.GetInt16(3);
                        yield return new PasswordLabel(id, name, type, index);
                    }
                }
            }
        }

        public void UpdatePassword(Password password)
        {
            if (password == null) throw new ArgumentNullException("password");
            try
            {
                using (var trans = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = new SqlCeCommand("update Password set [Name]=@name, IsFavorite=@isFave, Modified=@m where Id=@id", Connection))
                    {
                        command.Parameters.AddWithValue("@name", password.Name);
                        command.Parameters.AddWithValue("@isFave", password.IsFavorite);
                        command.Parameters.AddWithValue("@id", password.Id);
                        command.Parameters.AddWithValue("@m", DateTime.Now);
                        command.ExecuteNonQuery();
                    }
                    UpdatePasswordRelations(password);
                    trans.Commit();
                }
            }
            catch (Exception e)
            {
                if (Debugger.IsAttached) Debugger.Break();
                throw new DataBaseException("UpdatePassword", e);
            }
        }

        public void AddPassword(Password password)
        {
            if (password == null) throw new ArgumentNullException("password");
            try
            {
                using (var trans = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = new SqlCeCommand("insert into Password ([Name], IsFavorite,Modified) values(@name,@isFave,@m)", Connection))
                    {
                        command.Parameters.AddWithValue("@name", password.Name);
                        command.Parameters.AddWithValue("@isFave", password.IsFavorite);
                        command.Parameters.AddWithValue("@m", DateTime.Now);
                        command.ExecuteNonQuery();
                    }
                    UpdateId(password);
                    UpdatePasswordRelations(password);
                    trans.Commit();
                }
            }
            catch (Exception e)
            {
                if (Debugger.IsAttached) Debugger.Break();
                throw new DataBaseException("AddPassword", e);
            }
        }

        private void UpdateId(Entity entity)
        {
            using (SqlCeCommand command = new SqlCeCommand("select @@IDENTITY", Connection))
            {
                int id = (int)((decimal)command.ExecuteScalar());

                (entity as IIdAccess).SetId(id);
            }
        }

        private void UpdatePasswordRelations(Password password)
        {
            try
            {
                DeletePasswordRelations(password);
                if (password.Items.Count > 0)
                {
                    using (SqlCeCommand command = new SqlCeCommand("insert into PasswordItem (PasswordId, LabelId,Value,Modified) values(@id,@lid,@v,@m) ", Connection))
                    {
                        command.Parameters.AddWithValue("@id", password.Id);
                        command.Parameters.Add("@lid", SqlDbType.SmallInt);
                        command.Parameters.Add("@v", SqlDbType.NVarChar);
                        command.Parameters.AddWithValue("@m", DateTime.Now);
                        command.Prepare();
                        foreach (var item in password.Items)
                        {
                            if (item.ValueType >= ItemType.Internal) break;
                            command.Parameters[1].Value = item.LabelId;
                            command.Parameters[2].Value = item.Value;
                            command.ExecuteNonQuery();
                        }
                    }
                }

                if (password.Categories.Count > 0)
                {
                    using (SqlCeCommand command = new SqlCeCommand("insert into PasswordCategory (PasswordId, CategoryId) values(@id,@cid) ", Connection))
                    {
                        command.Parameters.AddWithValue("@id", password.Id);
                        command.Parameters.Add("@cid", SqlDbType.Int);
                        command.Prepare();
                        foreach (var c in password.Categories)
                        {
                            command.Parameters[1].Value = c.Id;
                            command.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception e)
            {
                if (Debugger.IsAttached) Debugger.Break();
                throw new DataBaseException("UpdatePasswordRelations", e);
            }
        }

        public void DeletePassword(Password password)
        {
            try
            {
                if (password == null) throw new ArgumentNullException("password");
                using (var trans = Connection.BeginTransaction())
                {
                    DeletePasswordRelations(password);
                    using (SqlCeCommand command = new SqlCeCommand("delete from Password where Id=@id", Connection))
                    {
                        command.Parameters.AddWithValue("@id", password.Id);
                        command.ExecuteScalar();
                    }
                    trans.Commit();
                }
            }
            catch (Exception e)
            {
                if (Debugger.IsAttached) Debugger.Break();
                throw new DataBaseException("DeletePassword", e);
            }
        }

        private void DeletePasswordRelations(Password password)
        {
            try
            {
                using (SqlCeCommand command = new SqlCeCommand("delete from PasswordItem where PasswordId=@id", Connection))
                {
                    command.Parameters.AddWithValue("@id", password.Id);
                    command.ExecuteScalar();
                }
                using (SqlCeCommand command = new SqlCeCommand("delete from PasswordCategory where PasswordId=@id", Connection))
                {
                    command.Parameters.AddWithValue("@id", password.Id);
                    command.ExecuteScalar();
                }
            }
            catch (Exception e)
            {
                throw new DataBaseException("DeletePasswordRelations", e);
            }
        }

        public void AddCategory(Category category)
        {
            try
            {
                using (SqlCeTransaction trans = Connection.BeginTransaction())
                {
                    using (var command = new SqlCeCommand("insert into category (Name,Modified) values(@name,@m)", Connection))
                    {
                        command.Parameters.AddWithValue("@name", category.Name);
                        command.Parameters.AddWithValue("@m", DateTime.Now);
                        command.ExecuteNonQuery();
                    }
                    UpdateId(category);
                    UpdateCategoryRelations(category);
                    trans.Commit();
                }
            }
            catch (Exception e)
            {
                throw new DataBaseException("AddCategory", e);
            }
        }

        public void UpdateCategory(Category category)
        {
            if (category == null) throw new ArgumentNullException("category");
            try
            {
                using (SqlCeTransaction trans = Connection.BeginTransaction())
                {
                    using (var command = new SqlCeCommand("update category set Name=@name, Modified=@m where Id=@id", Connection))
                    {
                        command.Parameters.AddWithValue("@name", category.Name);
                        command.Parameters.AddWithValue("@m", DateTime.Now);
                        command.Parameters.AddWithValue("@id", category.Id);
                        command.ExecuteNonQuery();
                    }
                    UpdateCategoryRelations(category);
                    trans.Commit();
                }
            }
            catch (Exception e)
            {
                throw new DataBaseException("UpdateCategory", e);
            }
        }

        private void UpdateCategoryRelations(Category category)
        {
            using (SqlCeCommand command = new SqlCeCommand("delete from PasswordCategory where CategoryId=@id", Connection))
            {
                command.Parameters.AddWithValue("@id", category.Id);
                command.ExecuteNonQuery();
            }
            using (SqlCeCommand command = new SqlCeCommand("delete from CategoryLabel where CategoryId=@id", Connection))
            {
                command.Parameters.AddWithValue("@id", category.Id);
                command.ExecuteNonQuery();
            }

            if (category.Passwords.Count > 0)
            {
                using (SqlCeCommand command = new SqlCeCommand("insert into PasswordCategory (CategoryId,PasswordId) values(@cid,@pid)", Connection))
                {
                    command.Parameters.AddWithValue("@cid", category.Id);
                    command.Parameters.Add("@pid", SqlDbType.Int);
                    command.Prepare();
                    foreach (var p in category.Passwords)
                    {
                        command.Parameters[1].Value = p.Id;
                        command.ExecuteNonQuery();
                    }
                }
            }
            if (category.Labels.Count > 0)
            {
                using (SqlCeCommand command = new SqlCeCommand("insert into CategoryLabel (CategoryId, LabelId) values(@cid,@lid)", Connection))
                {
                    command.Parameters.AddWithValue("@cid", category.Id);
                    command.Parameters.Add("@lid", SqlDbType.Int);
                    command.Prepare();
                    foreach (var l in category.Labels)
                    {
                        command.Parameters[1].Value = l.Id;
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        public void DeleteCategory(Category category)
        {
            if (category == null) throw new ArgumentNullException("category");
            try
            {
                using (var trans = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = new SqlCeCommand("delete from CategoryLabel where CategoryId=@id", Connection))
                    {
                        command.Parameters.AddWithValue("@id", category.Id);
                        command.ExecuteNonQuery();
                    }
                    using (SqlCeCommand command = new SqlCeCommand("delete from PasswordCategory where CategoryId=@id", Connection))
                    {
                        command.Parameters.AddWithValue("@id", category.Id);
                        command.ExecuteNonQuery();
                    }
                    using (SqlCeCommand command = new SqlCeCommand("delete from Category where Id=@id", Connection))
                    {
                        command.Parameters.AddWithValue("@id", category.Id);
                        command.ExecuteNonQuery();
                    }

                    trans.Commit();
                }
            }
            catch (Exception e)
            {
                throw new DataBaseException("DeleteCategory", e);
            }
        }

        public void UpdateLabel(PasswordLabel label)
        {
            if (label == null) throw new ArgumentNullException("label");
            using (SqlCeCommand command = new SqlCeCommand("update label  set name=@n, type=@t, [index]=@i, modified=@m where Id=@id", Connection))
            {
                command.Parameters.AddWithValue("@n", label.Name);
                command.Parameters.AddWithValue("@t", (short)label.ValueType);
                command.Parameters.AddWithValue("@i", (short)label.DisplayIndex);
                command.Parameters.AddWithValue("@m", DateTime.Now);
                command.Parameters.AddWithValue("@Id", label.Id);
                command.ExecuteNonQuery();
            }
        }

        public void InsertLabel(PasswordLabel label)
        {
            if (label == null) throw new ArgumentNullException("label");
            using (SqlCeTransaction trans = Connection.BeginTransaction())
            {
                using (SqlCeCommand command = new SqlCeCommand("insert into label (name,type,[index],modified) values(@n,@t,@i,@m)", Connection))
                {
                    command.Parameters.AddWithValue("@n", label.Name);
                    command.Parameters.AddWithValue("@t", (short)label.ValueType);
                    command.Parameters.AddWithValue("@i", (short)label.DisplayIndex);
                    command.Parameters.AddWithValue("@m", DateTime.Now);
                    command.ExecuteNonQuery();
                }
                UpdateId(label);
                trans.Commit();
            }
        }

        public void UpdateLabels(IEnumerable<PasswordLabel> labels)
        {
            if (labels == null) throw new ArgumentNullException("labels");
            using (SqlCeTransaction trans = Connection.BeginTransaction())
            {
                foreach (var label in labels)
                {
                    UpdateLabel(label);
                }
                trans.Commit();
            }
        }

        public void DeleteLabel(PasswordLabel label)
        {
            using (SqlCeTransaction trans = Connection.BeginTransaction())
            {
                using (SqlCeCommand command = new SqlCeCommand("delete from PasswordItem where Labelid=@id", Connection))
                {
                    command.Parameters.AddWithValue("@id", label.Id);
                }
                using (SqlCeCommand command = new SqlCeCommand("delete from CategoryLabel where Labelid=@id", Connection))
                {
                    command.Parameters.AddWithValue("@id", label.Id);
                }
                using (SqlCeCommand command = new SqlCeCommand("delete from Label where Id=@id", Connection))
                {
                    command.Parameters.AddWithValue("@id", label.Id);
                }
                trans.Commit();
            }
        }

        #endregion
    }
}
